package com.kai.controller;

import com.kai.service.UserService;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.Date;

/**
 * Excel的导入导出
 * king_zhang
 */
@RestController
@RequestMapping("excel")
public class ExcelController {

    @Resource
    private UserService userService;
    /***
     * 创建表头
     * @param workbook
     * @param sheet
     */
    private void createTitle(HSSFWorkbook workbook, HSSFSheet sheet)
    {
        HSSFRow row = sheet.createRow(0);
        //设置列宽，setColumnWidth的第二个参数要乘以256，这个参数的单位是1/256个字符宽度
        sheet.setColumnWidth(2, 12*256);
        sheet.setColumnWidth(3, 17*256);

        //设置为居中加粗
        HSSFCellStyle style = workbook.createCellStyle();
        HSSFFont font = workbook.createFont();
        font.setBold(true);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style.setFont(font);

        HSSFCell cell;
        cell = row.createCell(0);
        cell.setCellValue("序号");
        cell.setCellStyle(style);

        cell = row.createCell(1);
        cell.setCellValue("金额");
        cell.setCellStyle(style);

        cell = row.createCell(2);
        cell.setCellValue("描述");
        cell.setCellStyle(style);

        cell = row.createCell(3);
        cell.setCellValue("日期");
        cell.setCellStyle(style);
    }

    /**
     * 下载execl文档
     *
     */
    @RequestMapping("/download")
    public void download(HttpServletRequest request, HttpServletResponse response) throws Exception {

        OutputStream output = null;
        try {
           //创建HSSFWorkbook对象(excel的文档对象)
            HSSFWorkbook wkb = new HSSFWorkbook();
            //建立新的sheet对象（excel的表单）
            HSSFSheet sheet=wkb.createSheet("状态追踪查询导入模板");
            //在sheet里创建第一行，参数为行索引(excel的行)，可以是0～65535之间的任何一个
            HSSFRow row1=sheet.createRow(0);
            //创建单元格（excel的单元格，参数为列索引，可以是0～255之间的任何一个
            HSSFCell cell=row1.createCell(0);
            //设置单元格内容
            //cell.setCellValue("学员考试成绩一览表");
            //合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列
           // sheet.addMergedRegion(new CellRangeAddress(0,0,0,3));
            HSSFFont font = wkb.createFont();
            //设置字体颜色，加粗
            font.setBold(true);
            font.setColor((short)9);


            String color = "cbfdee";    //此处得到的color为16进制的字符串
            //转为RGB码
            //int r = Integer.parseInt((color.substring(0,2)),16);   //转为16进制
            //int g = Integer.parseInt((color.substring(2,4)),16);
            //int b = Integer.parseInt((color.substring(4,6)),16);
            //自定义cell颜色
            HSSFPalette palette = wkb.getCustomPalette();
            //这里的9是索引
            //palette.setColorAtIndex((short)9, (byte) r, (byte) g, (byte) b);
            //HSSFCellStyle style = wkb.createCellStyle();
            //style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            //setFillForegroundColor((short)9);

            HSSFCellStyle style = wkb.createCellStyle();
            style.setFillForegroundColor(IndexedColors.TEAL.getIndex());
            style.setFillPattern(CellStyle.SOLID_FOREGROUND);

            style.setFont(font);
            //设置居中
            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            sheet.setDefaultColumnWidth(20); // 设置列的默认宽度
            //在sheet里创建第二行,设置表头
           // HSSFRow row2=sheet.createRow(0);

            //创建单元格并设置单元格内容
            cell=row1.createCell(0);
            cell.setCellValue("序号");
            cell.setCellStyle(style);

            cell=row1.createCell(1);
            cell.setCellValue("进出口");
            cell.setCellStyle(style);

            cell=row1.createCell(2);
            cell.setCellValue("船名");
            cell.setCellStyle(style);

            cell=row1.createCell(3);
            cell.setCellValue("航次");
            cell.setCellStyle(style);

            cell=row1.createCell(4);
            cell.setCellValue("主单号");
            cell.setCellStyle(style);

            cell=row1.createCell(5);
            cell.setCellValue("分单号");
            cell.setCellStyle(style);

            cell=row1.createCell(6);
            cell.setCellValue("总箱数");
            cell.setCellStyle(style);

            //在sheet里创建第三行,导入数据

            // 设置默认文件名为当前时间：年月日时分秒
            String fileName = new SimpleDateFormat("yyyy-MM-dd HH-mm-ss").format(
                    new Date());
            String title = "提单号导入模版";
            // 如果文件名没提供，则使用时间戳
            if (fileName.trim().equals("")) {
                // 设置默认文件名为当前时间：年月日时分秒
                fileName = new SimpleDateFormat("yyyy-MM-dd HH-mm-ss")
                        .format(new Date());
            }

            //输出Excel文件
            output=response.getOutputStream();
            response.reset();

            //设置文件ContentType类型，这样设置，会自动判断下载文件类型
            response.setContentType("application/octet-stream");
            //设置文件头：最后一个参数是设置下载文件名 中文乱码问题
            response.setHeader( "Content-Disposition", "attachment;filename=" + new String( title.getBytes("gb2312"), "ISO8859-1" )+fileName+".xls");
            //response.setContentType("application/msexcel");
            wkb.write(output);
            output.close();

        } catch (IOException e) {
            e.printStackTrace();
        }finally{
            try {
                if(output != null){
                    output.close();
                }
            } catch (IOException e) {
                e.printStackTrace();

            }
        }
    }

}
